package com.augurit.gzcgwsp.state.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.jbpm.api.history.HistoryProcessInstance;
import org.jbpm.api.history.HistoryTask;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;

import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.excelimportexport.util.FileUtil;
import com.augurit.gzcgwsp.excelimportexport.util.GetFileName;
import com.augurit.gzcgwsp.state.dao.YwXzXkSpStateDao;
import com.augurit.gzcgwsp.state.service.IYwSummaryStateService;
import com.augurit.gzcgwsp.state.web.form.YwRqStateForm;
import com.augurit.gzcgwsp.wfex.common.service.IWfServiceEX;
import com.augurit.gzcgwsp.ywcmn.entity.BaseDistrict;

/**
 * 行政许可审批查询统计service实现类
 * 
 * @author 张军
 * 
 */
@Service
@Transactional
public class YwSummaryStateServiceImpl implements IYwSummaryStateService {
	private Logger log = Logger.getLogger(YwSummaryStateServiceImpl.class);
	@Autowired
	private IWfServiceEX wfServiceEX;
	@Autowired
	private YwXzXkSpStateDao ywXzXkSpStateDao;

	/**
	 * 分页查询
	 */
	public Page search(Page page, Map map) {
		String sql = this.buildSql(map);
		if("hwgg".equals((String)map.get("ywtype"))){
			sql=this.buildHwggSql(map);
		}
	
		// List list=this.ywXzXkSpStateDao.searchByPage(sql, page, map);
		page = this.ywXzXkSpStateDao.findPage(sql, page, map);
		// 转换page
		List<YwRqStateForm> list = this.convertFormList(page.getResult(), map);
		page.setResult(list);
		return page;

	}

	/**
	 * 根据查询的结果转换
	 * 
	 * @param list
	 * @return
	 */
	private List<YwRqStateForm> convertFormList(List list, Map params) {
		String queryresult = (String) params.get("result");// 页面查询是否同意不同意
		String ywtype=(String) params.get("ywtype");//户外广告单独处理
		List<YwRqStateForm> result = new ArrayList();
		if (list != null && list.size() > 0) {
			YwRqStateForm form = null;
			for (int k = 0; k < list.size(); k++) {
				Map map = (Map) list.get(k);
				String sqdw = (String) map.get("bus_memo1");
				String area = (String) map.get("bus_memo2");
				String state=(String) map.get("state_");//是否结案
				String templateName = (String) map.get("template_name");
				String file_code = (String) map.get("file_code");
				String slsj = (String) map.get("bus_date2");// 受理时间
				String jasj = (String) map.get("process_duedate");// 结案期限
				String acceptNo = (String) map.get("bus_memo3");
				String entityId=(String) map.get("wf_bus_instance_id");//流程实例id
				String procinst=(String) map.get("procinst_");
				form = new YwRqStateForm();
				form.setTemplateName(templateName);
				if(StringUtils.isNotBlank(jasj)){
					Date endDate=DateUtilsEX.getStringToDate(jasj,"yyyy-MM-dd");
					jasj=DateUtilsEX.dateTimeToString(endDate, "yyyy-MM-dd").substring(0,10);
					form.setJaqx(jasj);
					form.setProcDuedate(DateUtilsEX.getStringToDate(jasj,"yyyy-MM-dd"));
				}
				form.setProcInstId(procinst);
				if(StringUtils.isNotBlank(area)){
					form.setArea(area);
				}else{
					form.setArea("市城管委");
				}
				
				if(slsj.length()>=10){
					slsj=slsj.substring(0,10);
				}
				
				form.setProcStartdate(DateUtilsEX.getStringToDate(slsj,"yyyy-MM-dd"));
				form.setSlsj(DateUtilsEX.getStringToDate(slsj,"yyyy-MM-dd"));
				
				form.setSadate(slsj);
				form.setAcceptNo(acceptNo);
				if(StringUtils.isNotBlank(entityId)){
					form.setId(Long.valueOf(entityId));
				}
				form.setSqdw(sqdw);
				if("ended".equals(state)){
				 if(!"hwgg".equals(ywtype)){
				   if("bzcltzs".equals(file_code)||"qubzcltzs".equals(file_code)){
					  form.setAcceptResult("一次性告知");
				    }else if("zyxzxkjds".equals(file_code) ||"quzyxzxkjds".equals(file_code)){
				    	 form.setAcceptResult("准予许可");
				    }else if("byxkjds".equals(file_code)||"qubyxkjds".equals(file_code)){
				    	 form.setAcceptResult("不予许可");
				    }else {
				    	 form.setAcceptResult("其他");
				    }
				 }else{
					  if("byxkjds".equals(file_code)){
					    	 form.setAcceptResult("不予许可");
					    }else {
					    	 form.setAcceptResult("准予许可");
					    }
				 }
				}else if("active".equals(state)){
					form.setAcceptResult("办理中");
				}
				
				int diffday=wfServiceEX.getWorkDays(new Date(),form.getProcDuedate());
				//.calculateBussinessDays(new Date(),sform.getProcDuedate());
				form.setDiffday(diffday+"天");		
			   //查询办理进度
			   if("active".equals(state)){
				   String sql="select t.activity_chinese_name from JBPM4_HIST_TASK t where t.state_='open' and  t.procinst_='"+procinst+"'";
				   List data=ywXzXkSpStateDao.queryForList(sql);
				   String activityname="";
				   if (data != null && data.size() > 0) {
						for (int j = 0; j< data.size(); j++) {
							Map nameMap=(Map) data.get(0);
							activityname=(String) nameMap.get("activity_chinese_name");
							form.setProgress(activityname);
							break;
						}
				   }
			   }else{
					form.setProgress("已结案");
			   }
			   
				result.add(form);
			}
		}
		return result;
	}

	/**
	 * 生成hql
	 * 
	 * @param map
	 * @return
	 */
	private String buildSql(Map map) {
		// 查询语句及参数
		String acceptNo = (String) map.get("acceptNo");
		String sldate = (String) map.get("sqdate");
		String sqstartdate=(String) map.get("sqstartdate");
		if(StringUtils.isBlank(sqstartdate)){//当前年份
			sqstartdate=DateUtilsEX.getCurYear()+"-01-01";
		}
		String cbyw = (String) map.get("cbyw");
		String result = (String) map.get("result");
		String area=(String) map.get("area");//区域
		String progress=(String) map.get("progress");//查询办理进度
		String tempcodes=(String) map.get("tempcodes");//业务编码
		StringBuffer tempHql = new StringBuffer();
		
		if (StringUtils.isNotBlank(result)) {
			if("3".equals(result)){//结案处理结果为其他
				tempHql.append("select t.* \r");
			}else{
				tempHql.append("select t.*,doc.file_code \r");
			}
		}else{
			 tempHql.append("select t.*,doc.file_code \r");
		}
		
		tempHql.append("from(select distinct temp.template_code, ins.bus_date2, ins.bus_date1,ins.bus_memo1,ins.bus_memo3, \r");
		tempHql.append(" temp.template_name,ins.master_entity_key,proc.start_ ,ins.wf_bus_instance_id,proc.state_,task.procinst_,ins.bus_memo2 from \r");
		tempHql.append(" JBPM4_HIST_TASK task,JBPM4_HIST_PROCINST proc,WF_BUS_INSTANCE ins,WF_BUS_TEMPLATE temp \r");
		tempHql.append(" where task.procinst_=proc.id_ and proc.id_=ins.proc_inst_id \r");
		tempHql.append(" and ins.template_id=temp.id   \r");
		if(StringUtils.isNotBlank(progress)){
			if("1".equals(progress)){//经办人办理
				tempHql.append("  and  proc.state_='active'  and (task.activity_name like 'jbr%' or task.activity_name like '%gzry%')  and task.state_='open' \r");
			}else if("2".equals(progress)){//处领导审批
				tempHql.append("  and  proc.state_='active'   and (task.activity_name='czgldsh' or task.activity_name='cfgldsh') and task.state_='open'\r");
			}else if("3".equals(progress)){//委领导审批
				tempHql.append("  and  proc.state_='active'  and task.activity_name='wfgldsh' and task.state_='open' \r");
			}else if("4".equals(progress)){//窗口办理
				tempHql.append("  and  proc.state_='active'  and task.activity_name='cksa' and task.state_='open' \r");
			}
			else if("6".equals(progress)){//科长审批
				tempHql.append("  and  proc.state_='kzyj'  and task.activity_name='cksa' and task.state_='open' \r");
			}
			else if("7".equals(progress)){//局领导审批
				tempHql.append("  and  proc.state_ in ('syzxld','jfgld','syzxld')( and task.activity_name=''  and task.state_='open' \r");
			}else if("5".equals(progress)){//已经结案
				tempHql.append("  and  proc.state_='ended' \r");
			}else if("8".equals(progress)){//办理中
				tempHql.append("  and  proc.state_='active' \r");
			}
		}else{
			if (StringUtils.isNotBlank(result)) {//审批结果
				tempHql.append("  and proc.state_='ended' \r");
			}else{
				tempHql.append("  and ( proc.state_='ended' or  proc.state_='active') \r");
			}
		}
		if (StringUtils.isNotBlank(cbyw)) {
			tempHql.append(" and temp.template_code='" + cbyw + "'");
		} else {
			tempHql.append(" and temp.template_code in ("+tempcodes+")");
		}
		if (StringUtils.isNotBlank(sqstartdate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')>='"
					+ sqstartdate + "'");
			// tempHql.append(" and ins.bus_date1>= "+sdate);
			// tempHql.append(" and ins.bus_date1<= "+edate);
		}
		if (StringUtils.isNotBlank(sldate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')<='"
					+ sldate + "'");
			// tempHql.append(" and ins.bus_date1>= "+sdate);
			// tempHql.append(" and ins.bus_date1<= "+edate);
		}
		if (StringUtils.isNotBlank(acceptNo)) {
			tempHql.append(" and  ins.bus_memo3='" + acceptNo + "'");
		}
		if (StringUtils.isNotBlank(area) && !"市城管委".equals(area)) {
			tempHql.append(" and  ins.bus_memo2='" + area + "'");
		}else if(StringUtils.isNotBlank(area) && "市城管委".equals(area)){
			tempHql.append(" and  ins.bus_memo2 is null");
		}
		tempHql.append("   order by proc.start_ desc \r");

		if (StringUtils.isNotBlank(result)) {
			if ("0".equals(result)) {//不予许可
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and (doc.file_code ='byxkjds' or doc.file_code='qubyxkjds')\r");
			} else if ("1".equals(result)) {//准予许可
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and (doc.file_code='zyxzxkjds' or doc.file_code='quzyxzxkjds')\r");
			}else if("2".equals(result)){//一次性告知
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and doc.file_code='bzcltzs' \r");
			}else if("3".equals(result)){//其他，最后环节制作文书为空的
				tempHql.append(")  t where not exists  (select doc.id from YW_PRINT_DOC doc where doc.yw_id=t.master_entity_key \r");
				tempHql.append(" and doc.template_code=t.template_code and doc.file_code in ('byxkjds','bzcltzs','zyxzxkjds'))\r");
				
			}
		} else {
			tempHql.append(") t left join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
			tempHql.append(" and doc.file_code in ('byxkjds','zyxzxkjds','qubyxkjds','quzyxzxkjds') \r");
		}

		return tempHql.toString();
	}

	
	
	/**
	 * 单独生成户外广告hql
	 * 
	 * @param map
	 * @return
	 */
	private String buildHwggSql(Map map) {
		// 查询语句及参数
		String acceptNo = (String) map.get("acceptNo");
		String sldate = (String) map.get("sqdate");
		String sqstartdate=(String) map.get("sqstartdate");
		String cbyw = (String) map.get("cbyw");
		String result = (String) map.get("result");
		String progress=(String) map.get("progress");//查询办理进度
		String tempcodes=(String) map.get("tempcodes");//业务编码
		StringBuffer tempHql = new StringBuffer();
		
		if (StringUtils.isNotBlank(result)) {
			if("1".equals(result)){//结案处理结果为其他
				tempHql.append("select t.* \r");
			}else{
				tempHql.append("select t.*,doc.file_code \r");
			}
		}else{
			 tempHql.append("select t.*,doc.file_code \r");
		}
		
		tempHql.append("from(select distinct temp.template_code, ins.bus_date2, ins.bus_date1,ins.bus_memo1,ins.bus_memo3, \r");
		tempHql.append(" temp.template_name,ins.master_entity_key,proc.start_ ,ins.wf_bus_instance_id,proc.state_,task.procinst_,ins.bus_memo2 from \r");
		tempHql.append(" JBPM4_HIST_TASK task,JBPM4_HIST_PROCINST proc,WF_BUS_INSTANCE ins,WF_BUS_TEMPLATE temp \r");
		tempHql.append(" where task.procinst_=proc.id_ and proc.id_=ins.proc_inst_id \r");
		tempHql.append(" and ins.template_id=temp.id   \r");
		if(StringUtils.isNotBlank(progress)){
			if("1".equals(progress)){//经办人办理
				tempHql.append("  and  proc.state_='active'  and (task.activity_name like 'jbr%' or task.activity_name like '%gzry%')  and task.state_='open' \r");
			}else if("2".equals(progress)){//处领导审批
				tempHql.append("  and  proc.state_='active'   and (task.activity_name='czgldsh' or task.activity_name='cfgldsh') and task.state_='open'\r");
			}else if("3".equals(progress)){//委领导审批
				tempHql.append("  and  proc.state_='active'  and task.activity_name='wfgldsh' and task.state_='open' \r");
			}else if("4".equals(progress)){//窗口办理
				tempHql.append("  and  proc.state_='active'  and task.activity_name='cksa' and task.state_='open' \r");
			}
			else if("6".equals(progress)){//科长审批
				tempHql.append("  and  proc.state_='kzyj'  and task.activity_name='cksa' and task.state_='open' \r");
			}
			else if("7".equals(progress)){//局领导审批
				tempHql.append("  and  proc.state_ in ('syzxld','jfgld','syzxld')( and task.activity_name=''  and task.state_='open' \r");
			}else if("5".equals(progress)){//已经结案
				tempHql.append("  and  proc.state_='ended' \r");
			}
		}else{
			if (StringUtils.isNotBlank(result)) {//审批结果
				tempHql.append("  and proc.state_='ended' \r");
			}else{
				tempHql.append("  and ( proc.state_='ended' or  proc.state_='active') \r");
			}
		}
		if (StringUtils.isNotBlank(cbyw)) {
			tempHql.append(" and temp.template_code='" + cbyw + "'");
		} else {
			tempHql.append(" and temp.template_code in ("+tempcodes+")");
		}
		if (StringUtils.isNotBlank(sqstartdate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')>='"
					+ sqstartdate + "'");
		}
		if (StringUtils.isNotBlank(sldate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')<='"
					+ sldate + "'");
		}
		if (StringUtils.isNotBlank(acceptNo)) {
			tempHql.append(" and  ins.bus_memo3='" + acceptNo + "'");
		}
		tempHql.append("   order by proc.start_ desc \r");

		if (StringUtils.isNotBlank(result)) {
			if ("0".equals(result)) {//不予许可
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and doc.file_code ='byxkjds' \r");
			} else if ("1".equals(result)) {//准予许可,户外广告同意申请的没有文书
				tempHql.append(")  t where not exists  (select doc.id from YW_PRINT_DOC doc where doc.yw_id=t.master_entity_key \r");
				tempHql.append(" and doc.template_code=t.template_code and doc.file_code in ('byxkjds','bzcltzs','zyxzxkjds'))\r");
		
			}else if("2".equals(result)){//一次性告知
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and doc.file_code='bzcltzs' \r");
			}else if("3".equals(result)){//其他，户外广告没有其他类型，目前只有燃气处有其他类型。此处随便加了一个文号就会不存在数据
				tempHql.append(") t inner join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
				tempHql.append(" and doc.file_code='bzcltzss' \r");
				
			}
		} else {
			tempHql.append(") t left join YW_PRINT_DOC doc on doc.yw_id=t.master_entity_key and doc.template_code=t.template_code \r");
			tempHql.append(" and doc.file_code in ('byxkjds','bzcltzs','zyxzxkjds') \r");
		}


		return tempHql.toString();
	}


	/**
	 * 获取页面业务编码
	 */
	public List getCpyws(String ywname) {
		String sql=" select t.template_code, t.template_name from WF_BUS_TEMPLATE t ,WF_BUS_TEMPLATE_TYPE type where";
		if(StringUtils.isNotBlank(ywname)){
			sql+=" t.template_type_id=type.id and t.is_active='1' and type.template_type_name='"+ywname+"'";
		}else{
			sql+=" t.template_type_id=type.id and t.is_active='1'";
		}
		sql+=" order by t.id asc";
		List list=this.ywXzXkSpStateDao.queryForList(sql);
		return list;
	}

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.state.service.IYwSummaryStateService#getArea()
	 */
	@Override
	public List<BaseDistrict> getArea() {
		String sql=" from BaseDistrict where parentDistrictId=-1 order by id asc";
		List list=this.ywXzXkSpStateDao.find(sql);
		return list;
	}

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.state.service.IYwSummaryStateService#searchDbData(org.springside.modules.orm.Page, java.util.Map)
	 */
	@Override
	public Page searchDbData(Page page, Map map) {
		String sql=this.dbSql(map);
		page = this.ywXzXkSpStateDao.findPage(sql, page, map);
		// 转换page
		List<YwRqStateForm> list = this.convertFormList(page.getResult(), map);
		page.setResult(list);
		return page;
	}
	
	/**
	 * 获取待办sql
	 * @return
	 */
	private String dbSql(Map map){
		// 查询语句及参数
		String acceptNo = (String) map.get("acceptNo");
		String sldate = (String) map.get("sqdate");
		String sqstartdate=(String) map.get("sqstartdate");
		String cbyw = (String) map.get("cbyw");
		String result = (String) map.get("result");
		String area=(String) map.get("area");//区域
		String progress=(String) map.get("progress");//查询办理进度
		String tempcodes=(String) map.get("tempcodes");//业务编码
		StringBuffer tempHql = new StringBuffer();
		
		if (StringUtils.isNotBlank(result)) {
			if("3".equals(result)){//结案处理结果为其他
				tempHql.append("select t.* \r");
			}else{
				tempHql.append("select t.* \r");
			}
		}else{
			 tempHql.append("select t.*\r");
		}
		
		tempHql.append("from(select distinct temp.template_code, ins.bus_date2, ins.bus_date1,ins.bus_memo1,ins.bus_memo3, \r");
		tempHql.append(" temp.template_name,ins.master_entity_key,proc.start_ ,ins.wf_bus_instance_id,proc.state_,task.procinst_,ins.bus_memo2,proc.process_duedate from \r");
		tempHql.append(" JBPM4_HIST_TASK task,JBPM4_HIST_PROCINST proc,WF_BUS_INSTANCE ins,WF_BUS_TEMPLATE temp \r");
		tempHql.append(" where task.procinst_=proc.id_ and proc.id_=ins.proc_inst_id \r");
		tempHql.append(" and ins.template_id=temp.id   \r");
			if (StringUtils.isNotBlank(result)) {//审批结果
				tempHql.append("  and   task.state_='open'  proc.state_='active'\r");
			}else{
				tempHql.append("  and (   task.state_='open'  and proc.state_='active') \r");
			}
		
		if (StringUtils.isNotBlank(cbyw)) {
			tempHql.append(" and temp.template_code='" + cbyw + "'");
		} else {
			tempHql.append(" and temp.template_code in ("+tempcodes+")");
		}
		if (StringUtils.isNotBlank(sqstartdate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')>='"
					+ sqstartdate + "'");
			// tempHql.append(" and ins.bus_date1>= "+sdate);
			// tempHql.append(" and ins.bus_date1<= "+edate);
		}
		if (StringUtils.isNotBlank(sldate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')<='"
					+ sldate + "'");
			// tempHql.append(" and ins.bus_date1>= "+sdate);
			// tempHql.append(" and ins.bus_date1<= "+edate);
		}
		if (StringUtils.isNotBlank(acceptNo)) {
			tempHql.append(" and  ins.bus_memo3='" + acceptNo + "'");
		}
		if (StringUtils.isNotBlank(area)) {
			tempHql.append(" and  ins.bus_memo2='" + area + "'");
		}
		tempHql.append("   order by  proc.process_duedate) t \r");
		return tempHql.toString();
	}

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.state.service.IYwSummaryStateService#searchYjData(org.springside.modules.orm.Page, java.util.Map)
	 */
	@Override
	public Page searchYjData(Page page, Map map) {
		String sql=this.getYjsql(map);
		
		page = this.ywXzXkSpStateDao.findPage(sql, page, map);
		// 转换page
		List<YwRqStateForm> list = convertYwYnJzfqwczcJbxxFormList(page.getResult(), map);
		page.setResult(list);
		return page;
	}
  
	/**
	 * 转换运输企业表单
	 * @param list
	 * @param paramMap
	 * @return
	 */
	private List<YwRqStateForm> convertYwYnJzfqwczcJbxxFormList(List list,
			Map paramMap) {

		List<YwRqStateForm> result = new ArrayList();
		if (list != null && list.size() > 0) {
			YwRqStateForm form = null;
			for (int k = 0; k < list.size(); k++) {
				Map map = (Map) list.get(k);
				String sqdw = (String) map.get("bus_memo1");
				String area = (String) map.get("bus_memo2");
				String state=(String) map.get("state_");//是否结案
				String templateName = (String) map.get("template_name");
				if(templateName.split("==>").length>1){
					templateName=templateName.split("==>")[1];
				}
				String file_code = (String) map.get("file_code");
				String slsj = (String) map.get("bus_date2");// 受理时间
				String jasj = (String) map.get("process_duedate");// 结案期限
				String acceptNo = (String) map.get("bus_memo3");
				String entityId=(String) map.get("wf_bus_instance_id");//流程实例id
				String procinst=(String) map.get("procinst_");
				String zsbh=(String)map.get("certificate_no");
				String starttime=(String)map.get("valid_starttime");
				String endtime=(String)map.get("valid_endtime");
				form = new YwRqStateForm();
				form.setTemplateName(templateName);
				form.setProcInstId(procinst);
				form.setArea(area);
				form.setAcceptNo(acceptNo);
				if(StringUtils.isNotBlank(entityId)){
					form.setId(Long.valueOf(entityId));
				}
				if(StringUtils.isNotBlank(endtime) && endtime.length()>=10){
					endtime=endtime.substring(0,10);
				}
				
				if(StringUtils.isNotBlank(starttime) && starttime.length()>=10){
					starttime=starttime.substring(0,10);
				}
				form.setZsyxqsj(starttime+"~"+endtime);
				
				if(slsj.length()>=10){
					slsj=slsj.substring(0,10);
					form.setSadate(slsj);
				}
				if(StringUtils.isNotBlank(jasj)){
					Date endDate=DateUtilsEX.getStringToDate(jasj,"yyyy-MM-dd");
					jasj=DateUtilsEX.dateTimeToString(endDate, "yyyy-MM-dd").substring(0,10);
					form.setJaqx(jasj);
					form.setProcDuedate(DateUtilsEX.getStringToDate(jasj,"yyyy-MM-dd"));
				}
				
				form.setSqdw(sqdw);
				form.setZsbh(zsbh);
				result.add(form);
			}
		}
		return result;
	
	}

	/**
	 * 获取已结的sql
	 * @param map
	 * @return
	 */
	private String getYjsql(Map map){
		// 查询语句及参数
		String acceptNo = (String) map.get("acceptNo");
		String sldate = (String) map.get("sqdate");
		String sqstartdate=(String) map.get("sqstartdate");
		String cbyw = (String) map.get("cbyw");
		String result = (String) map.get("result");
		String area=(String) map.get("area");//区域
		String progress=(String) map.get("progress");//查询办理进度
		String tempcodes=(String) map.get("tempcodes");//业务编码
		StringBuffer tempHql = new StringBuffer();
		
		if (StringUtils.isNotBlank(result)) {
			if("3".equals(result)){//结案处理结果为其他
				tempHql.append("select t.* \r");
			}else{
				tempHql.append("select t.* \r");
			}
		}else{
			 tempHql.append("select t.*\r");
		}
		
		tempHql.append("from(select distinct temp.template_code,ysczz.certificate_no,ysczz.valid_endtime,ysczz.valid_starttime, ins.bus_date2, ins.bus_date1,ins.bus_memo1,ins.bus_memo3, \r");
		tempHql.append(" temp.template_name,ins.master_entity_key,proc.start_ ,ins.wf_bus_instance_id,proc.state_,task.procinst_,ins.bus_memo2,proc.process_duedate from \r");
		tempHql.append(" JBPM4_HIST_TASK task,JBPM4_HIST_PROCINST proc,YW_YN_JZFQWCZC_JBXX ysczz,WF_BUS_INSTANCE ins,WF_BUS_TEMPLATE temp \r");
		tempHql.append(" where task.procinst_=proc.id_ and proc.id_=ins.proc_inst_id and ysczz.id=ins.master_entity_key \r");
		tempHql.append(" and ins.template_id=temp.id   \r");
			if (StringUtils.isNotBlank(result)) {//审批结果
				tempHql.append("  and   proc.state_='ended' \r");
			}else{
				tempHql.append("  and   proc.state_='ended'   \r");
			}
		
		if (StringUtils.isNotBlank(cbyw)) {
			tempHql.append(" and temp.template_code='" + cbyw + "'");
		} else {
			tempHql.append(" and temp.template_code in ("+tempcodes+")");
		}
		if (StringUtils.isNotBlank(sqstartdate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')>='"
					+ sqstartdate + "'");
			// tempHql.append(" and ins.bus_date1>= "+sdate);
			// tempHql.append(" and ins.bus_date1<= "+edate);
		}
		if (StringUtils.isNotBlank(sldate)) {
			tempHql.append(" and to_char(ins.bus_date2,'yyyy-MM-dd')<='"
					+ sldate + "'");
			// tempHql.append(" and ins.bus_date1>= "+sdate);
			// tempHql.append(" and ins.bus_date1<= "+edate);
		}
		if (StringUtils.isNotBlank(acceptNo)) {
			tempHql.append(" and  ins.bus_memo3='" + acceptNo + "'");
		}
		if (StringUtils.isNotBlank(area)) {
			tempHql.append(" and  ins.bus_memo2='" + area + "'");
		}
		tempHql.append("   order by  proc.process_duedate) t \r");
		return tempHql.toString();
	
	}


}
